package com.appstore.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.appstore.dao.IApplicationDao;
import com.appstore.exception.DaoException;
import com.appstore.model.Application;
import com.appstore.util.ConnectionManager;
import com.appstore.util.DateFormatUtil;

/**
 * THe implementation of IApplicationDao
 * 
 * @author zhbhun
 * 
 */
public class ApplicationDaoImpl implements IApplicationDao {

	private static ApplicationDaoImpl applicationDaoImpl;
	private ConnectionManager connectionManager;

	private ApplicationDaoImpl() {
		connectionManager = ConnectionManager.getConnectionManager();
	}

	/**
	 * instance ApplicationDaoImpl
	 * 
	 * @return
	 */
	public static ApplicationDaoImpl createInstatce() {
		if (applicationDaoImpl == null) {
			applicationDaoImpl = new ApplicationDaoImpl();
		}
		return applicationDaoImpl;
	}

	/**
	 * get the instance of ApplicationDaoImpl
	 * 
	 * @return
	 */
	public static ApplicationDaoImpl getApplicationDaoImpl() {
		return applicationDaoImpl;
	}

	@Override
	public void save(Application application) throws DaoException {
		Connection connection = null;
		// get connection
		try {
			connection = connectionManager.getConnetion();
		} catch (SQLException e) {
			throw new DaoException("can not get connection", e);
		}
		// save data
		try {
			String sql = "insert into application (name,size,created_time,icon,description,screenshot,dislike,_like,two_diment_code,deleted,download_url,dowload_count,version,updated_time) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			connection.setAutoCommit(false);
			PreparedStatement pStatement = connection.prepareStatement(sql);
			pStatement.setString(1, application.getName());
			pStatement.setString(2, application.getSize());
			pStatement.setObject(3, application.getCreatedTime());
			pStatement.setString(4, application.getIcon());
			pStatement.setString(5, application.getDescription());
			pStatement.setString(6, application.getScreenShot());
			pStatement.setInt(7, application.getDislike());
			pStatement.setInt(8, application.getLike());
			pStatement.setString(9, application.getTwoDimentCode());
			pStatement.setBoolean(10, application.getDeleted());
			pStatement.setString(11, application.getDownloadUrl());
			pStatement.setInt(12, application.getDownloadCount());
			pStatement.setString(13, application.getVersion());
			pStatement.setObject(14, application.getUpdatedTime());
			pStatement.executeUpdate();
			pStatement.close();
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				throw new DaoException("there is error while rollback", e);
			}
			throw new DaoException("there is error while executing sql", e);
		}
		// close connection
		try {
			connection.close();
		} catch (SQLException e) {
			throw new DaoException("can not close connection", e);
		}

	}

	@Override
	public List<Application> findAll() throws DaoException {
		List<Application> list = new ArrayList<Application>();
		Connection connection = null;
		// get connection
		try {
			connection = connectionManager.getConnetion();
		} catch (SQLException e) {
			throw new DaoException("can not get connection", e);
		}
		// find data
		try {
			String sql = "select * from application order by updated_time desc";
			connection.setAutoCommit(false);
			PreparedStatement pStatement = connection.prepareStatement(sql);
			ResultSet resultSet = pStatement.executeQuery();
			while (resultSet.next()) {
				Application app = this.instanceApplication(resultSet);
				list.add(app);
			}
			resultSet.close();
			pStatement.close();
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				throw new DaoException("there is error while rollback", e);
			}
			throw new DaoException("there is error while executing sql", e);
		}
		// close connection
		try {
			connection.close();
		} catch (SQLException e) {
			throw new DaoException("can not close connection", e);
		}
		return list;
	}

	@Override
	public Application findbyId(Integer id) throws DaoException {
		Application app = null;
		Connection connection = null;
		// get connection
		try {
			connection = connectionManager.getConnetion();
		} catch (SQLException e) {
			throw new DaoException("can not get connection", e);
		}
		// find data
		try {
			String sql = "select * from application where id = ?";
			connection.setAutoCommit(false);
			PreparedStatement pStatement = connection.prepareStatement(sql);
			pStatement.setInt(1, id);
			ResultSet resultSet = pStatement.executeQuery();
			while (resultSet.next()) {
				app = this.instanceApplication(resultSet);
			}
			resultSet.close();
			pStatement.close();
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				throw new DaoException("there is error while rollback", e);
			}
			throw new DaoException("there is error while executing sql", e);
		}
		// close connection
		try {
			connection.close();
		} catch (SQLException e) {
			throw new DaoException("can not close connection", e);
		}
		return app;
	}

	@Override
	public void update(Application application) throws DaoException {
		Connection connection = null;
		// get connection
		try {
			connection = connectionManager.getConnetion();
		} catch (SQLException e) {
			throw new DaoException("can not get connection", e);
		}
		// save data
		try {
			String sql = "update application set ";
			sql += " name = '" + application.getName();
			sql += "', size = '" + application.getSize();
			sql += "', updated_time = '"
			        + DateFormatUtil.sqlFormat(application.getUpdatedTime());
			sql += "', icon = '" + application.getIcon();
			sql += "', description = '" + application.getDescription();
			sql += "', screenshot = '" + application.getScreenShot();
			sql += "', dislike = " + application.getDislike();
			sql += ", _like = " + application.getLike();
			sql += ", two_diment_code = '" + application.getTwoDimentCode();
			sql += "', deleted = ";
			if (application.getDeleted()) {
				sql += 1;
			} else {
				sql += 0;
			}
			sql += ", download_url = '" + application.getDownloadUrl();
			sql += "', dowload_count = " + application.getDownloadCount();
			sql += ", version = '" + application.getVersion()
			        + "' where id =? ";
			connection.setAutoCommit(false);
			PreparedStatement pStatement = connection.prepareStatement(sql);
			pStatement.setInt(1, application.getId());
			pStatement.executeUpdate();
			pStatement.close();
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				throw new DaoException("there is error while rollback", e);
			}
			throw new DaoException("there is error while executing sql", e);
		}
		// close connection
		try {
			connection.close();
		} catch (SQLException e) {
			throw new DaoException("can not close connection", e);
		}

	}

	private Application instanceApplication(ResultSet resultSet)
	        throws SQLException {
		Application application = new Application();
		application.setId(resultSet.getInt("id"));
		application.setName(resultSet.getString("name"));
		application.setIcon(resultSet.getString("icon"));
		application.setSize(resultSet.getString("size"));
		application.setCreatedTime(resultSet.getTimestamp("created_time"));
		application.setUpdatedTime(resultSet.getTimestamp("updated_time"));
		application.setLike(resultSet.getInt("_like"));
		application.setDislike(resultSet.getInt("dislike"));
		application.setDescription(resultSet.getString("description"));
		application.setDeleted(resultSet.getBoolean("deleted"));
		application.setScreenShot(resultSet.getString("screenshot"));
		application.setTwoDimentCode(resultSet.getString("two_diment_code"));
		application.setDownloadUrl(resultSet.getString("download_url"));
		application.setDownloadCount(resultSet.getInt("dowload_count"));
		application.setVersion(resultSet.getString("version"));
		return application;
	}

	@Override
	public void delete(int id) throws DaoException {
		Connection connection = null;
		// get connection
		try {
			connection = connectionManager.getConnetion();
		} catch (SQLException e) {
			throw new DaoException("can not get connection", e);
		}
		// find data
		try {
			String sql = "delete from application where id = ?";
			connection.setAutoCommit(false);
			PreparedStatement pStatement = connection.prepareStatement(sql);
			pStatement.setInt(1, id);
			pStatement.executeUpdate();
			pStatement.close();
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				throw new DaoException("there is error while rollback", e);
			}
			throw new DaoException("there is error while executing sql", e);
		}
		// close connection
		try {
			connection.close();
		} catch (SQLException e) {
			throw new DaoException("can not close connection", e);
		}
	}

	@Override
	public void addDownloadCount(int id) throws DaoException {
		Connection connection = null;
		// get connection
		try {
			connection = connectionManager.getConnetion();
		} catch (SQLException e) {
			throw new DaoException("can not get connection", e);
		}
		// update data
		try {
			String sql = "update application set dowload_count = dowload_count + 1 where id = ?";
			connection.setAutoCommit(false);
			PreparedStatement pStatement = connection.prepareStatement(sql);
			pStatement.setInt(1, id);
			pStatement.executeUpdate();
			pStatement.close();
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				throw new DaoException("there is error while rollback", e);
			}
			throw new DaoException("there is error while executing sql", e);
		}
		// close connection
		try {
			connection.close();
		} catch (SQLException e) {
			throw new DaoException("can not close connection", e);
		}

	}
}
